package com.zjdiepu.www.manage.api.dz;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import cn.jiguang.common.utils.StringUtils;

import com.zjdiepu.www.base.BaseController;
import com.zjdiepu.www.base.core.AbstractSysUser;
import com.zjdiepu.www.base.core.ApiResponse;
import com.zjdiepu.www.base.core.PageVo;
import com.zjdiepu.www.base.enums.status.BusiStatus;
import com.zjdiepu.www.base.enums.status.DpStatus;
import com.zjdiepu.www.base.enums.type.DataDicType;
import com.zjdiepu.www.base.enums.type.ModeIDType;
import com.zjdiepu.www.base.enums.type.OperateType;
import com.zjdiepu.www.base.enums.type.ResultFlagType;
import com.zjdiepu.www.common.util.CollectionInitUtil;
import com.zjdiepu.www.common.util.ExcelUtil;
import com.zjdiepu.www.common.util.ZhppDateUtil;
import com.zjdiepu.www.commpent.mapper.yw.YwBusinessVoMapper;
import com.zjdiepu.www.config.FileProperty;
import com.zjdiepu.www.ibiz.cache.ICachedDictionaryService;
import com.zjdiepu.www.ibiz.dz.IContractBindingService;
import com.zjdiepu.www.ibiz.dz.IDzLoanMiddleAuditService;
import com.zjdiepu.www.ibiz.dz.IOtherPrintLogService;
import com.zjdiepu.www.ibiz.other.IOtherAuditInfoService;
import com.zjdiepu.www.ibiz.third.IJuheCityService;
import com.zjdiepu.www.ibiz.third.IJuheProvinceService;
import com.zjdiepu.www.ibiz.yw.IYwBusinessExtrasService;
import com.zjdiepu.www.ibiz.yw.IYwBusinessService;
import com.zjdiepu.www.ibiz.zx.IZxCreditQueryService;
import com.zjdiepu.www.model.base.dto.SysUser;
import com.zjdiepu.www.model.dz.dto.DzLoanMiddleAudit;
import com.zjdiepu.www.model.dz.dto.OtherPrintLog;
import com.zjdiepu.www.model.dz.ext.DzLoanMiddleAuditExt;
import com.zjdiepu.www.model.dz.vo.DzContractBindingVo;
import com.zjdiepu.www.model.other.dto.OtherAuditInfo;
import com.zjdiepu.www.model.other.ext.OtherAuditInfoExt;
import com.zjdiepu.www.model.yw.dto.YwBusiness;
import com.zjdiepu.www.model.yw.dto.YwBusinessExtras;
import com.zjdiepu.www.model.yw.request.YwBusinessQueryRequest;
import com.zjdiepu.www.model.zx.dto.ZxCreditQuery;

@RestController
@RequestMapping(value= "ContractbindingController", produces="application/json;charset=UTF-8")
@Api(tags = {"合同套打"}, produces="application/json;charset=UTF-8")
public class ContractbindingController extends BaseController{
	@Resource
	private IContractBindingService contractBindingService;
	@Resource
    private IOtherAuditInfoService otherAuditInfoService;
	@Resource
    private IZxCreditQueryService zxCreditQueryService;
	@Resource
    private ICachedDictionaryService cachedDictionaryService;
	@Resource
	private FileProperty fileProperty;
	@Resource
	private IOtherPrintLogService otherPrintLogService;
	@Resource
    private IYwBusinessService ywBusinessService;
	@Resource
    private YwBusinessVoMapper ywBusinessVoMapper;
	@Resource
	private IDzLoanMiddleAuditService dzLoanMiddleAuditService;
	@Resource
	private IJuheProvinceService juheProvinceService;
	@Resource 
	private IJuheCityService juheCityService;
	@Resource
	private IYwBusinessExtrasService ywBusinessExtrasService;
	/**
	 * @param model
	 * @return
	 */
	@ApiOperation(value = "显示合同字典", response = String.class, httpMethod="POST", tags={"查询"})
    @RequestMapping(value = "showContractDicList", method = RequestMethod.POST)
	public ApiResponse showContractDicList(Model model){
		model.addAttribute("carContractType", cachedDictionaryService.getValueNamesWithSpaceOption(DataDicType.CAR_CONTRACT_TYPE,"请选择..."));
		return ApiResponse.success(model);
	}
	@ApiOperation(value = "检查是否被锁单", response = String.class, httpMethod="POST", tags={"查询"})
    @RequestMapping(value = "checkLoanEditLock", method = RequestMethod.POST)
	public ApiResponse checkLoanEditLock(String busiId){
		YwBusinessExtras ywBusinessExtras=new YwBusinessExtras();
		ywBusinessExtras.setBusiId(busiId);
		ywBusinessExtras.setDpStatus(DpStatus.NORMAL.getCode());
		ywBusinessExtras.setCodeExtras("LoanEditLock");
		ywBusinessExtras=ywBusinessExtrasService.selectRefined(ywBusinessExtras);
		if(ywBusinessExtras!=null && ywBusinessExtras.getValueExtras().equals("1")){
			return ApiResponse.fail(ResultFlagType.WUFA_TAODA);
		}
		return ApiResponse.success(true);
	}
		
	@ApiOperation(value = "下载合同", response = Boolean.class, httpMethod="GET", tags={"下载合同"})
    @RequestMapping(value = "download", method = RequestMethod.GET)
	public ApiResponse download(HttpServletResponse response,String prefix,String busiId,String bank) {  
		
		List<DzContractBindingVo> list=contractBindingService.downloadContract(busiId);
		OtherAuditInfoExt otherAuditInfoExt= new OtherAuditInfoExt();
		otherAuditInfoExt.setBusiId(busiId);
		otherAuditInfoExt.setOrderSql("created desc");
		otherAuditInfoExt.withOutPage();
		String path="";
		String pathTo="";
		String bocName="";
		if(bank !=null && bank.equals("BOC")){
			bocName=fileProperty.getContractBOCName();
		}else{
			bocName=fileProperty.getContractName();
		}
		try {
			HttpSession session = request.getSession();      
			ServletContext  application  = session.getServletContext();  
			//xiognxiaotun 2017-7-8
			String serverRealPath = application.getRealPath("/WEB-INF");
			serverRealPath = serverRealPath.replaceAll("\\\\", "/");
			path = serverRealPath + "/" + fileProperty.getTemplet()+new String(bocName.getBytes("ISO-8859-1"),"UTF-8");
	        pathTo=serverRealPath + "/" + new String(bocName.getBytes("ISO-8859-1"),"UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		if(list ==null || list.size()==0){
			return ApiResponse.fail(100300,ResultFlagType.MISSING.getValue());
		}else{
			ExcelUtil.excelCopy(new File(path), new File(pathTo));
			//单个文件复制
//			ExcelUtil.copyFile(path, pathTo);
			if(bank !=null && bank.contains("ICBC")){
				//审批信息列表(多条)
				otherAuditInfoExt.setOrderSql(" CREATED DESC");
				List<OtherAuditInfo> listOtherAuditInfo=otherAuditInfoService.selectByExt(otherAuditInfoExt);
				//征信信息(多条)
				List<ZxCreditQuery> listZxCreditQuery=contractBindingService.selectByBusiId(busiId);
				importExcel(list,pathTo,listOtherAuditInfo,listZxCreditQuery,prefix);
			}else{
				importBOCExcel(list,pathTo);
			}
			downloadExcel(pathTo,response,list.get(0).getName());
			//合同套打日志
			saveLog(list);
		}
		return ApiResponse.success(true);
    }  
	
	@ApiOperation(value = "提交合同", response = Boolean.class, httpMethod="POST", tags={"提交"})
    @RequestMapping(value = "confirmContract", method = RequestMethod.POST)
	public ApiResponse confirmContract(String busiId){
		DzLoanMiddleAuditExt dzLoanMiddleAuditExt= new DzLoanMiddleAuditExt();
		dzLoanMiddleAuditExt.setBusiId(busiId);
		dzLoanMiddleAuditExt.setOrderSql(" created desc");
		dzLoanMiddleAuditExt.setDpStatus(1);
		List<DzLoanMiddleAudit> list=dzLoanMiddleAuditService.selectByExt(dzLoanMiddleAuditExt);
		dzLoanMiddleAuditService.confirmContract(list,busiId,getCurrentUser());
		return ApiResponse.success(true);
	}
	/**
	 * 写入中行模板
	 * @param list
	 * @param pathTo
	 */
	private void importBOCExcel(List<DzContractBindingVo> list, String pathTo) {
		//删除列表中是担保人的数据
		for(int i=0;i<list.size();i++){
			if(list.get(i).getCarManType().equals("02")){
				list.remove(i);
			}
		}
		InputStream ins = null;
        Workbook wb = null;     
        try {
			ins=new FileInputStream(new File(pathTo));
			wb= new HSSFWorkbook(ins);
			ins.close();     
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}  
        DecimalFormat    df   = new DecimalFormat("######0.00");   
        Sheet sheet=wb.getSheetAt(0);
		 if (sheet==null) {
	            sheet = (Sheet) wb.createSheet("sheet1");  
	        }
	        double carPrice=list.get(0).getCarPrice();
	    	Row row1 = sheet.getRow(1);
	    	HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle();
	    	style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
	    	Font font = wb.createFont();
	        font.setFontHeightInPoints((short)13); 
	        font.setColor(HSSFColor.RED.index);   
	        style.setFont(font);
	    	if(list.size()>1){
	    		row1.createCell(1).setCellValue(list.get(0).getName());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
	    			row1.createCell(3).setCellValue(list.get(1).getName());
	    			row1.getCell(3).setCellStyle(style);
	    		}
	    		String contractNo="";
	    		contractNo=list.get(0).getContractNo()==null?"":list.get(0).getContractNo();
	    		row1.createCell(5).setCellValue(contractNo);
	    		row1.getCell(5).setCellStyle(style);
	    		
	    		row1 = sheet.getRow(2); 
	    		row1.createCell(1).setCellValue(list.get(0).getIdno().toString());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
		    		row1.createCell(3).setCellValue(list.get(1).getIdno());
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(cachedDictionaryService.getName(list.get(0).getColorCar(),DataDicType.CAR_COLOR));
	    		row1.getCell(5).setCellStyle(style);
	    		
	    		row1 = sheet.getRow(3); 
	    		row1.createCell(1).setCellValue(list.get(0).getFamilyAddr());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
	    			row1.createCell(3).setCellValue(list.get(0).getFamilyAddr());
	    			row1.getCell(3).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(list.get(0).getCxname());
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(4); 
	    		row1.createCell(1).setCellValue(list.get(0).getTel1());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
		    		row1.createCell(3).setCellValue(list.get(1).getTel1());
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(list.get(0).getVin());
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(5); 
	    		row1.createCell(1).setCellValue(list.get(0).getNowTel());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
	    			row1.createCell(3).setCellValue(list.get(1).getNowTel());
	    			row1.getCell(3).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(list.get(0).getNumEngine());
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(6); 
	    		row1.createCell(1).setCellValue(list.get(0).getPostcode());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
		    		row1.createCell(3).setCellValue(list.get(0).getPostcode());
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		Date dat=list.get(0).getDateBill();
	    		String dateBill="";
	    		if(dat!=null){
	    			dateBill=ZhppDateUtil.DateToString(dat, "yyyy-MM-dd");
	    		}
	    		row1.createCell(5).setCellValue(dateBill);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(7); 
	    		row1.createCell(1).setCellValue(list.get(0).getCompany());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
		    		row1.createCell(3).setCellValue(list.get(1).getCompany());
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(list.get(0).getLoanMonth()/12);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(8); 
	    		row1.createCell(1).setCellValue(list.get(0).getCompanyAddr());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
		    		row1.createCell(3).setCellValue(list.get(1).getCompanyAddr());
		    		row1.getCell(3).setCellStyle(style);
	    		}
//	    		row1.createCell(5).setCellValue(list.get(0).getBankRate());
	    		row1.createCell(5).setCellValue(list.get(0).getBankRate()==null?0: list.get(0).getBankRate()*100);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(9); 
	    		row1.createCell(1).setCellValue(list.get(0).getMonthIncome());
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
		    		row1.createCell(3).setCellValue(list.get(1).getMonthIncome());
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(carPrice);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(10); 
	    		row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getJob(),DataDicType.ZHI_WEI));
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
	    			row1.createCell(3).setCellValue(cachedDictionaryService.getName(list.get(1).getJob(),DataDicType.ZHI_WEI));
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(list.get(0).getDealerName());
	    		row1.getCell(5).setCellStyle(style);
	    		
	    		row1 = sheet.getRow(11); 
	    		row1.createCell(1).setCellValue("身份证");
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
	    			row1.createCell(3).setCellValue(cachedDictionaryService.getName(list.get(1).getSex(),DataDicType.SEX));
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		String registration=list.get(0).getRegionRegistration();
	    		if(StringUtils.isEmpty(registration)){
	    			String spdc=juheCityService.getNameById(list.get(0).getAreaRegistration());
	    			spdc=spdc==null?"":spdc;
	    			String spdp=juheProvinceService.getNameById(list.get(0).getProvinceRegistration());
	    			spdp=spdp==null?"":spdp;
	    			if(spdc.equals("") && spdp.equals("")){
	    				registration=list.get(0).getRegistrationArea();
	    			}else{
	    				registration=spdp+spdc;
	    			}
	    		}
	    		row1.createCell(5).setCellValue(registration);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(12); 
	    		row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getSex(),DataDicType.SEX));
	    		row1.getCell(1).setCellStyle(style);
	    		if(list.get(1).getCarManType().equals("01")){
	    			row1.createCell(3).setCellValue("身份证");
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(cachedDictionaryService.getName(list.get(0).getCarType(),DataDicType.CAR_TYPE));
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(13); 
	    		if(list.get(1).getCarManType().equals("01")){
	    			row1.createCell(3).setCellValue("");//身份证号码没有
		    		row1.getCell(3).setCellStyle(style);
	    		}
	    		
	    	}else{
	    		row1.createCell(1).setCellValue(list.get(0).getName());
	    		row1.getCell(1).setCellStyle(style);
	    		String contractNo="";
	    		contractNo=list.get(0).getContractNo()==null?"":list.get(0).getContractNo();
	    		row1.createCell(5).setCellValue(contractNo);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(2); 
	    		row1.createCell(1).setCellValue(list.get(0).getIdno().toString());
	    		row1.getCell(1).setCellStyle(style);
	    		row1.createCell(5).setCellValue(cachedDictionaryService.getName(list.get(0).getColorCar(),DataDicType.CAR_COLOR));
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(3); 
	    		row1.createCell(1).setCellValue(list.get(0).getFamilyAddr());
	    		row1.getCell(1).setCellStyle(style);
	    		row1.createCell(5).setCellValue(list.get(0).getCxname());
	    		row1.getCell(5).setCellStyle(style);
	    		
	    		row1 = sheet.getRow(4); 
	    		row1.createCell(1).setCellValue(list.get(0).getTel1());
	    		row1.getCell(1).setCellStyle(style);
	    		row1.createCell(5).setCellValue(list.get(0).getVin());
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(5); 
	    		row1.createCell(1).setCellValue(list.get(0).getNowTel());
	    		row1.getCell(1).setCellStyle(style);
	    		row1.createCell(5).setCellValue(list.get(0).getNumEngine());
	    		row1.getCell(5).setCellStyle(style);
	    		Date dat=list.get(0).getDateBill();
	    		row1 = sheet.getRow(6); 
	    		row1.createCell(1).setCellValue(list.get(0).getPostcode());
	    		row1.getCell(1).setCellStyle(style);
	    		String dateBill="";
	    		if(dat!=null){
	    			dateBill=ZhppDateUtil.DateToString(dat, "yyyy-MM-dd");
	    		}
	    		row1.createCell(5).setCellValue(dateBill);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(7); 
	    		row1.createCell(1).setCellValue(list.get(0).getCompany());
	    		row1.getCell(1).setCellStyle(style);
	    		row1.createCell(5).setCellValue(list.get(0).getLoanMonth()/12);
	    		row1.getCell(5).setCellStyle(style);
	    		
	    		
	    		row1 = sheet.getRow(8); 
	    		row1.createCell(1).setCellValue(list.get(0).getCompanyAddr());
	    		row1.getCell(1).setCellStyle(style);
//	    		row1.createCell(5).setCellValue(list.get(0).getBankRate());
	    		row1.createCell(5).setCellValue(list.get(0).getBankRate()==null?0: list.get(0).getBankRate()*100);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(9); 
	    		row1.createCell(1).setCellValue(list.get(0).getMonthIncome());
	    		row1.getCell(1).setCellStyle(style);
	    		row1.createCell(5).setCellValue(carPrice);
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(10); 
	    		row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getJob(),DataDicType.ZHI_WEI));
	    		row1.getCell(1).setCellStyle(style);
	    		row1.createCell(5).setCellValue(list.get(0).getDealerName());
	    		row1.getCell(5).setCellStyle(style);
	    		
	    		row1 = sheet.getRow(11); 
	    		row1.createCell(1).setCellValue("身份证");
	    		row1.getCell(1).setCellStyle(style);
//	    		row1.createCell(5).setCellValue(list.get(0).getProvinceRegistration());
	    		String registration=list.get(0).getRegionRegistration();
	    		if(StringUtils.isEmpty(registration)){
		    		String spdc=juheCityService.getNameById(list.get(0).getAreaRegistration());
		    		spdc=spdc==null?"":spdc;
		    		String spdp=juheProvinceService.getNameById(list.get(0).getProvinceRegistration());
		    		spdp=spdp==null?"":spdp;
		    		if(spdc.equals("") && spdp.equals("")){
		    			registration=list.get(0).getRegistrationArea();
		    		}else{
		    			registration=spdp+spdc;
		    		}
	    		}
	    		row1.createCell(5).setCellValue(registration);
	    		
	    		row1.getCell(5).setCellStyle(style);
	    		row1 = sheet.getRow(12); 
	    		if(list.get(0).getSex()!=null){
	    			row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getSex(),DataDicType.SEX));
	    			row1.getCell(1).setCellStyle(style);
	    		}
	    		row1.createCell(5).setCellValue(cachedDictionaryService.getName(list.get(0).getCarType(),DataDicType.CAR_TYPE));
	    		row1.getCell(5).setCellStyle(style);
	    	}
	    	row1 = sheet.getRow(13); 
	    	row1.createCell(1).setCellValue("");
	    	row1.getCell(1).setCellStyle(style);
	    	row1.createCell(5).setCellValue(list.get(0).getFamilyRegister());
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(14); 
	    	row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getMaritalStatus(),DataDicType.MARITAL_STATUS));
	    	row1.getCell(1).setCellStyle(style);
	    	row1.createCell(5).setCellValue(list.get(0).getNumQualified());
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(15); 
	    	row1.createCell(1).setCellValue(list.get(0).getFamilyTel());
	    	row1.getCell(1).setCellStyle(style);
	    	row1.createCell(5).setCellValue(list.get(0).getAreaBill());
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(16); 
	    	row1.createCell(1).setCellValue(list.get(0).getCompanyTel());
	    	row1.getCell(1).setCellStyle(style);
//	    	row1.createCell(5).setCellValue(list.get(0).getCompanyRate());
	    	row1.createCell(5).setCellValue(list.get(0).getCompanyRate()==null?0: Double.parseDouble(convertBigDecimal(Double.parseDouble(String.valueOf(list.get(0).getCompanyRate()*100)),4)));
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(17); 
	    	row1.createCell(5).setCellValue(list.get(0).getRealLoanAmount());
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(18); 
	    	row1.createCell(1).setCellValue(list.get(0).getContact1());
	    	row1.getCell(1).setCellStyle(style);
	    	row1.createCell(5).setCellValue(list.get(0).getAmountMonthly());
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(19); 
	    	row1.createCell(1).setCellValue(list.get(0).getContact1Tel1());
	    	row1.getCell(1).setCellStyle(style);
	    	row1.createCell(5).setCellValue(list.get(0).getMonthlyPrincipalAmount());
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(20); 
	    	row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getContact1Relation(),DataDicType.CONTACT_RELATION));
	    	row1.getCell(1).setCellStyle(style);
	    	row1.createCell(5).setCellValue(list.get(0).getFirstMonthFee()==null?0:Math.ceil(list.get(0).getFirstMonthFee()*100)/100);
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(21); 
	    	row1.createCell(5).setCellValue(list.get(0).getMonthlyFee()==null?0:Double.parseDouble(df.format(list.get(0).getMonthlyFee())));
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(22); 
	    	row1.createCell(5).setCellValue(list.get(0).getStagesFee()==null?0:Double.parseDouble(list.get(0).getStagesFee().toString()));
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(23); 
	    	row1.createCell(1).setCellValue(list.get(0).getNowAddr());
	    	row1.getCell(1).setCellStyle(style);
	    	row1.createCell(3).setCellValue(list.get(0).getCompanyAddr());
	    	row1.getCell(3).setCellStyle(style);
	    	row1.createCell(5).setCellValue(list.get(0).getStagesFee2()==null?0:Double.parseDouble(list.get(0).getStagesFee2().toString()));
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(24); 
	    	row1.createCell(5).setCellValue(list.get(0).getFirstMonthFee2()==null?0:Double.parseDouble(list.get(0).getFirstMonthFee2().toString()));
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(25); 
	    	row1.createCell(5).setCellValue(list.get(0).getMonthlyFee2()==null?0:Double.parseDouble(list.get(0).getMonthlyFee2().toString()));
	    	row1.getCell(5).setCellStyle(style);
	    	
	    	row1 = sheet.getRow(26); 
	    	row1.createCell(5).setCellValue(list.get(0).getFirstPaymentAmount());
	    	row1.getCell(5).setCellStyle(style);
	    	
	        //创建文件流   
	        OutputStream stream;
			try {
				stream = new FileOutputStream(pathTo);
				//写入数据   
				wb.write(stream);  
				//关闭文件流   
				stream.close();  
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}  
		
	}
	/**
	 * 保存合同套打日志
	 * @param list
	 */
	private void saveLog(List<DzContractBindingVo> list) {
		AbstractSysUser currentUser = getCurrentUser();
		OtherPrintLog otherPrintLog= new OtherPrintLog();
		DzContractBindingVo contractVo= list.get(0);
		
		otherPrintLog.setCreator(currentUser.getId());
		otherPrintLog.setCreatorName(currentUser.getName());
		otherPrintLog.setModifier(currentUser.getId()); //统一使用修改人和修改人名称来初始化
		otherPrintLog.setModifierName(currentUser.getName());
		otherPrintLog.setBusiId(contractVo.getId());
		otherPrintLog.setCusId(contractVo.getCustomerId());
		otherPrintLog.setDepartmentSalesman(contractVo.getOrgName());
		otherPrintLog.setNameSalesman(contractVo.getSalesmanName());
		otherPrintLog.setNameCust(contractVo.getName());
		otherPrintLog.setNoCust(contractVo.getIdno());
		otherPrintLog.setTimeContractHandPrint(contractVo.getPickCarTime());
		otherPrintLog.setOperatorContractPrint(currentUser.getId());
		otherPrintLog.setTimeContractPrint(new Date());
		otherPrintLogService.saveUpdateAndDelete(otherPrintLog, OperateType.add);
		
	}
	/**
	 * 下载合同套打
	 * @param pathTo
	 * @param response
	 */
	private void downloadExcel(String pathTo,HttpServletResponse response,String name) {
		try {    
			// path是指欲下载的文件的路径。    
			File file = new File(pathTo);    
			// 取得文件名。    
			String filename = file.getName();   
			if(filename.contains(".")){
				String[] filenames= filename.split("\\.");
				if(filenames[0].equals("ICBC")){
					filename="工商银行"+"-"+name+"\\."+filenames[1];
				}else{
					filename="中国银行"+"-"+name+"\\."+filenames[1];
				}
			}
			// 以流的形式下载文件。    
			InputStream fis = new BufferedInputStream(new FileInputStream(pathTo));    
			byte[] buffer = new byte[fis.available()];    
			fis.read(buffer);    
			fis.close();    
			// 清空response    
			response.reset();    
			// 设置response的Header    
			response.addHeader("Content-Disposition", "attachment;filename="    
					+ new String(filename.getBytes("UTF-8"), "ISO-8859-1"));    
			response.addHeader("Content-Length", "" + file.length());    
			OutputStream toClient = new BufferedOutputStream(    
					response.getOutputStream());    
			response.setContentType("application/vnd.ms-excel;charset=gb2312");    
			toClient.write(buffer);    
			toClient.flush();    
			toClient.close();    
		} catch (IOException ex) {    
			ex.printStackTrace();    
		}    
		
	}
	/**
	 * 把合同内容写入excel
	 * @param list
	 * @param pathTo
	 */
	private void importExcel(List<DzContractBindingVo> list, String pathTo,
			List<OtherAuditInfo> listOtherAuditInfo,
			List<ZxCreditQuery> listZxCreditQuery,String prefix) {
		InputStream ins = null;
        Workbook wb = null;     
        try {
			ins=new FileInputStream(new File(pathTo));
			wb= new XSSFWorkbook(ins);
			ins.close();     
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}  
        Sheet sheet=wb.getSheetAt(0);
         //创建sheet对象   
        if (sheet==null) {
            sheet = (Sheet) wb.createSheet("sheet1");  
        }
        String creditPeopleType="";
        XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
    	style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    	Font font = wb.createFont();
        font.setFontHeightInPoints((short)13); 
        font.setColor(HSSFColor.RED.index);   
        style.setFont(font);
        //写入征信记录
        String resultMark="";
        if(listZxCreditQuery !=null && listZxCreditQuery.size()>0){
        	for(int i=0;i<listZxCreditQuery.size();i++){
        		creditPeopleType=listZxCreditQuery.get(i).getCreditPeopleType();
        		if(creditPeopleType==null) continue;
        		if(listZxCreditQuery.get(i).getCreditResultMark()==null) continue;
        		resultMark=listZxCreditQuery.get(i).getCreditResultMark().replaceAll("</?[^>]+>", "").replaceAll("&nbsp;", "");
        		if(creditPeopleType.equals("03")){
        			Row row=sheet.getRow(1);
        			row.createCell(7).setCellValue(resultMark);
        			row.getCell(7).setCellStyle(style);
        		}else if(creditPeopleType.equals("01")){
        			Row row=sheet.getRow(2);
        			row.createCell(7).setCellValue(resultMark);
        			row.getCell(7).setCellStyle(style);
        		}else{
        			Row row=sheet.getRow(3);
        			if(row.getCell(7)!=null){
        				Row row1=sheet.getRow(4);
        				row1.createCell(7).setCellValue(resultMark);
        				row1.getCell(7).setCellStyle(style);
        			}else{
        				row.createCell(7).setCellValue(resultMark);
        				row.getCell(7).setCellStyle(style);
        			}
        		}
        	}
        }
        //写入审批记录
        OtherAuditInfo otherAuditInfo=new OtherAuditInfo();
        if(listOtherAuditInfo !=null && listOtherAuditInfo.size()>0){
        	String auditDetail="";
        	String auditPostil="";
        	for(int i=0;i<listOtherAuditInfo.size();i++){
        		otherAuditInfo=listOtherAuditInfo.get(i);
        		if(otherAuditInfo.getAuditDetail()!=null && otherAuditInfo.getAuditPostil()!=null){
        			auditDetail=otherAuditInfo.getAuditDetail().replaceAll("</?[^>]+>", "").replaceAll("&nbsp;", "");
        			auditPostil=otherAuditInfo.getAuditPostil().replaceAll("</?[^>]+>", "").replaceAll("&nbsp;", "");
        		}
        		if(otherAuditInfo!=null && otherAuditInfo.getModeId()!=null){
        			if(otherAuditInfo.getModeId().equals(ModeIDType.CSDCS.getCode())){
        				Row row1=sheet.getRow(5);
        				row1.createCell(7).setCellValue(auditDetail);
        				row1.getCell(7).setCellStyle(style);
        				Row row2=sheet.getRow(6);
        				row2.createCell(7).setCellValue(auditPostil);
        				row2.getCell(7).setCellStyle(style);
        			}else if(otherAuditInfo.getModeId().equals(ModeIDType.DSCS.getCode())){
        				Row row1=sheet.getRow(7);
        				row1.createCell(7).setCellValue(auditDetail);
        				row1.getCell(7).setCellStyle(style);
        				Row row2=sheet.getRow(8);
        				row2.createCell(7).setCellValue(auditPostil);
        				row2.getCell(7).setCellStyle(style);
        			}
        			
        		}
        	}
        }
        double carPrice=list.get(0).getCarPrice()==null?0:list.get(0).getCarPrice();
        double signLoanAmount=list.get(0).getSignLoanAmount()==null?0:list.get(0).getSignLoanAmount();
    	Row row1 = sheet.getRow(1);
    	
    	if(list.size()>1){
    		row1.createCell(1).setCellValue(list.get(0).getName());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    			row1.createCell(3).setCellValue(list.get(1).getName());
    			row1.getCell(3).setCellStyle(style);
    		}
    		row1.createCell(5).setCellValue("");
    		
    		row1 = sheet.getRow(2); 
    		row1.createCell(1).setCellValue(list.get(0).getIdno().toString());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    			row1.createCell(3).setCellValue(list.get(1).getIdno().toString());
    			row1.getCell(3).setCellStyle(style);
    		}
    		Cell cell2=row1.createCell(5);
    		cell2.setCellValue(list.get(0).getCxname());
    		cell2.setCellStyle(style);
    		row1 = sheet.getRow(3); 
    		row1.createCell(1).setCellValue(list.get(0).getFamilyAddr());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
	    		row1.createCell(3).setCellValue(list.get(0).getFamilyAddr());
	    		row1.getCell(3).setCellStyle(style);
    		}
    		Cell cell3=row1.createCell(5);
    		cell3.setCellValue(list.get(0).getVin());
    		
    		cell3.setCellStyle(style);
    		row1 = sheet.getRow(4); 
    		row1.createCell(1).setCellValue(list.get(0).getTel1());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    			Cell cell4=row1.createCell(3);
    			cell4.setCellValue(list.get(1).getTel1());
    			
				cell4.setCellStyle(style);
    		}
    		Cell cell4=row1.createCell(5);
    		cell4.setCellValue(list.get(0).getNumEngine());
    		
    		cell4.setCellStyle(style);
    		row1 = sheet.getRow(5); 
    		row1.createCell(1).setCellValue(list.get(0).getNowTel());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
	    		row1.createCell(3).setCellValue(list.get(1).getNowTel());
	    		row1.getCell(3).setCellStyle(style);
    		}
    		Date dat=list.get(0).getDateBill();
    		String dateBill="";
    		if(dat!=null){
    			dateBill=ZhppDateUtil.DateToString(dat, "yyyy-MM-dd");
    		}
    		Cell cell5=row1.createCell(5);
    		cell5.setCellValue(dateBill);
    		cell5.setCellStyle(style);
    		
    		row1 = sheet.getRow(6); 
    		row1.createCell(1).setCellValue(list.get(0).getPostcode());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    			row1.createCell(3).setCellValue(list.get(0).getPostcode());
    			row1.getCell(3).setCellStyle(style);
    		}
    		Cell cell6=row1.createCell(5);
    		cell6.setCellValue(list.get(0).getLoanMonth()/12);
    		cell6.setCellStyle(style);
    		
    		row1 = sheet.getRow(7); 
    		row1.createCell(1).setCellValue(list.get(0).getCompany());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    			row1.createCell(3).setCellValue(list.get(1).getCompany());
    			row1.getCell(3).setCellStyle(style);
    		}
    		Cell cell7=row1.createCell(5);
    		cell7.setCellValue(list.get(0).getBankRate()==null?0: list.get(0).getBankRate()*100);
    		cell7.setCellStyle(style);
    		
    		row1 = sheet.getRow(8); 
    		row1.createCell(1).setCellValue(list.get(0).getCompanyAddr());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    			Cell cell8=row1.createCell(3);
	    		cell8.setCellValue(list.get(1).getCompanyAddr());
				cell8.setCellStyle(style);
    		}
    		Cell cell8=row1.createCell(5);
    		cell8.setCellValue(list.get(0).getSignLoanAmount());
    		cell8.setCellStyle(style);
    		
    		row1 = sheet.getRow(9); 
    		row1.createCell(1).setCellValue(list.get(0).getMonthIncome());
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    			Cell cell9=row1.createCell(3);
	    		cell9.setCellValue(list.get(1).getMonthIncome());
	    		
				cell9.setCellStyle(style);
    		}
    		Cell cell9=row1.createCell(5);
    		cell9.setCellValue(carPrice);
    		cell9.setCellStyle(style);
    		
    		row1 = sheet.getRow(10); 
    		row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getJob(),DataDicType.ZHI_WEI));
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
	    		row1.createCell(3).setCellValue(cachedDictionaryService.getName(list.get(1).getSex(),DataDicType.SEX));
	    		row1.getCell(3).setCellStyle(style);
    		}
    		Cell cell10=row1.createCell(5);
    		cell10.setCellValue(list.get(0).getDealerName());
    		
    		cell10.setCellStyle(style);
    		
    		row1 = sheet.getRow(11); 
    		row1.createCell(1).setCellValue("身份证");
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    		row1.createCell(3).setCellValue("身份证");
    		row1.getCell(3).setCellStyle(style);
    		}
    		Cell cell11=row1.createCell(5);
//    		cell11.setCellValue(list.get(0).getProvinceRegistration());
    		String registration=list.get(0).getRegionRegistration();
    		if(StringUtils.isEmpty(registration)){
	    		String spdc=juheCityService.getNameById(list.get(0).getAreaRegistration());
	    		spdc=spdc==null?"":spdc;
	    		String spdp=juheProvinceService.getNameById(list.get(0).getProvinceRegistration());
	    		spdp=spdp==null?"":spdp;
	    		if(spdc.equals("") && spdp.equals("")){
	    			registration=list.get(0).getRegistrationArea();
	    		}else{
	    			registration=spdp+spdc;
	    		}
    		}
    		row1.createCell(5).setCellValue(registration);
			cell11.setCellStyle(style);
    		
    		row1 = sheet.getRow(12); 
    		row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getSex(),DataDicType.SEX));
    		row1.getCell(1).setCellStyle(style);
    		if(list.get(1).getCarManType().equals("01")){
    		row1.createCell(3).setCellValue("");
    		row1.getCell(3).setCellStyle(style);
    		}
    		Cell cell12=row1.createCell(5);
    		cell12.setCellValue(cachedDictionaryService.getName(list.get(0).getCarType(),DataDicType.CAR_TYPE));
    		cell12.setCellStyle(style);
    	}else{
    		row1.createCell(1).setCellValue(list.get(0).getName());
    		row1.getCell(1).setCellStyle(style);
//    		String contractNo="";
//    		contractNo=list.get(0).getContractNo()==null?"":list.get(0).getContractNo();
    		row1.createCell(5).setCellValue("");
    		row1.getCell(5).setCellStyle(style);
    		row1 = sheet.getRow(2); 
    		row1.createCell(1).setCellValue(list.get(0).getIdno().toString());
    		row1.getCell(1).setCellStyle(style);
    		Cell cell2=row1.createCell(5);
    		cell2.setCellValue(list.get(0).getCxname());
    		
    		cell2.setCellStyle(style);
    		
    		row1 = sheet.getRow(3); 
    		row1.createCell(1).setCellValue(list.get(0).getFamilyAddr());
    		row1.getCell(1).setCellStyle(style);
    		Cell cell3=row1.createCell(5);
    		cell3.setCellValue(list.get(0).getVin());
    		
    		cell3.setCellStyle(style);
    		
    		row1 = sheet.getRow(4); 
    		row1.createCell(1).setCellValue(list.get(0).getTel1());
    		row1.getCell(1).setCellStyle(style);
    		Cell cell4=row1.createCell(5);
    		cell4.setCellValue(list.get(0).getNumEngine());
    		
    		cell4.setCellStyle(style);
    		
    		row1 = sheet.getRow(5); 
    		row1.createCell(1).setCellValue(list.get(0).getNowTel());
    		row1.getCell(1).setCellStyle(style);
    		Date dat=list.get(0).getDateBill();
    		String dateBill="";
    		if(dat!=null){
    			dateBill=ZhppDateUtil.DateToString(dat, "yyyy-MM-dd");
    		}
    		Cell cell5=row1.createCell(5);
    		cell5.setCellValue(dateBill);
    		
    		cell5.setCellStyle(style);
    		
    		row1 = sheet.getRow(6); 
    		row1.createCell(1).setCellValue(list.get(0).getPostcode());
    		row1.getCell(1).setCellStyle(style);
    		Cell cell6=row1.createCell(5);
    		cell6.setCellValue(list.get(0).getLoanMonth()/12);
    		
    		cell6.setCellStyle(style);
    		
    		row1 = sheet.getRow(7); 
    		row1.createCell(1).setCellValue(list.get(0).getCompany());
    		row1.getCell(1).setCellStyle(style);
    		Cell cell7=row1.createCell(5);
    		cell7.setCellValue(list.get(0).getBankRate()==null?0: list.get(0).getBankRate()*100);
    		cell7.setCellStyle(style);
    		
    		row1 = sheet.getRow(8); 
    		row1.createCell(1).setCellValue(list.get(0).getCompanyAddr());
    		row1.getCell(1).setCellStyle(style);
    		Cell cell8=row1.createCell(5);
    		cell8.setCellValue(list.get(0).getSignLoanAmount()==null?0:list.get(0).getSignLoanAmount());
    		
    		cell8.setCellStyle(style);
    		
    		row1 = sheet.getRow(9); 
    		row1.createCell(1).setCellValue(list.get(0).getMonthIncome());
    		row1.getCell(1).setCellStyle(style);
    		Cell cell9=row1.createCell(5);
    		cell9.setCellValue(carPrice);
    		
    		cell9.setCellStyle(style);
    		
    		row1 = sheet.getRow(10); 
    		row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getJob(),DataDicType.ZHI_WEI));
    		row1.getCell(1).setCellStyle(style);
    		Cell cell10=row1.createCell(5);
    		cell10.setCellValue(list.get(0).getDealerName());
    		
    		cell10.setCellStyle(style);
    		
    		row1 = sheet.getRow(11); 
    		row1.createCell(1).setCellValue("身份证");
    		row1.getCell(1).setCellStyle(style);
    		Cell cell11=row1.createCell(5);
//    		cell11.setCellValue(list.get(0).getProvinceRegistration());
    		String registration=list.get(0).getRegionRegistration();
    		if(StringUtils.isEmpty(registration)){
	    		String spdc=juheCityService.getNameById(list.get(0).getAreaRegistration());
	    		spdc=spdc==null?"":spdc;
	    		String spdp=juheProvinceService.getNameById(list.get(0).getProvinceRegistration());
	    		spdp=spdp==null?"":spdp;
	    		if(spdc.equals("") && spdp.equals("")){
	    			registration=list.get(0).getRegistrationArea();
	    		}else{
	    			registration=spdp+spdc;
	    		}
    		}
    		row1.createCell(5).setCellValue(registration);
			cell11.setCellStyle(style);
    		
    		row1 = sheet.getRow(12); 
    		if(list.get(0).getSex()!=null){
    			row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getSex(),DataDicType.SEX));
    			row1.getCell(1).setCellStyle(style);
    		}
    		Cell cell12=row1.createCell(5);
    		cell12.setCellValue(cachedDictionaryService.getName(list.get(0).getCarType(),DataDicType.CAR_TYPE));
    		
    		cell12.setCellStyle(style);
    	}
    	row1 = sheet.getRow(13); 
    	row1.createCell(1).setCellValue("");
    	row1.getCell(1).setCellStyle(style);
    	Cell cell13=row1.createCell(5);
    	cell13.setCellValue(list.get(0).getFamilyRegister());
    	
    	cell13.setCellStyle(style);
    	
    	row1 = sheet.getRow(14); 
    	row1.createCell(1).setCellValue(cachedDictionaryService.getName(list.get(0).getMaritalStatus(),DataDicType.MARITAL_STATUS));
    	row1.getCell(1).setCellStyle(style);
    	Cell cell14=row1.createCell(5);
    	cell14.setCellValue(list.get(0).getNumQualified());
    	
    	cell14.setCellStyle(style);
    	
    	row1 = sheet.getRow(15); 
    	row1.createCell(1).setCellValue(list.get(0).getFamilyTel());
    	row1.getCell(1).setCellStyle(style);
    	Cell cell15=row1.createCell(5);
    	cell15.setCellValue(list.get(0).getAreaBill());
    	
    	cell15.setCellStyle(style);
    	
    	row1 = sheet.getRow(16); 
    	row1.createCell(1).setCellValue(list.get(0).getCompanyTel());
    	row1.getCell(1).setCellStyle(style);
    	row1.createCell(5).setCellValue(list.get(0).getFirstPaymentAmount()==null?0:list.get(0).getFirstPaymentAmount());
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(17); 
    	row1.createCell(5).setCellValue(list.get(0).getIcbcAmountMonthly()==null?0:Math.ceil(list.get(0).getIcbcAmountMonthly()));
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(18); 
    	row1.createCell(5).setCellValue(list.get(0).getIcbcAmountMonthly()==null?100:Math.ceil(list.get(0).getIcbcAmountMonthly())+100);
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(19); 
    	String contractNo="";
		contractNo=list.get(0).getContractNo()==null?"":list.get(0).getContractNo();
    	row1.createCell(5).setCellValue(prefix+contractNo);
    	row1.getCell(5).setCellStyle(style);
    	if(!prefix.equals("")){
    		row1.createCell(6).setCellValue(prefix+"0"+contractNo);
    		row1.getCell(6).setCellStyle(style);
    	}
    	
    	row1 = sheet.getRow(20); 
    	row1.createCell(5).setCellValue(list.get(0).getCompanyRate()==null?0: Double.parseDouble(convertBigDecimal(Double.parseDouble(String.valueOf(list.get(0).getCompanyRate()*100)),4)));
//    	row1.createCell(5).setCellValue(list.get(0).getCompanyRate()==null?0:list.get(0).getCompanyRate());
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(21); 
    	row1.createCell(5).setCellValue(list.get(0).getLoanAmount()==null?0:list.get(0).getLoanAmount());
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(22); 
    	row1.createCell(5).setCellValue(list.get(0).getOrgName());
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(23); 
    	row1.createCell(5).setCellValue(list.get(0).getSalesmanName());
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(24); 
    	row1.createCell(5).setCellValue(list.get(0).getTotalRepaymentAmount()==null?0:list.get(0).getTotalRepaymentAmount());
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(25); 
//    	row1.createCell(5).setCellValue(list.get(0).getIcbcStagesFee()==null?0:list.get(0).getIcbcStagesFee());
    	row1.createCell(5).setCellValue(list.get(0).getIcbcStagesFee()==null?0: Double.parseDouble(convertBigDecimal(Double.parseDouble(String.valueOf(list.get(0).getIcbcStagesFee())),2)));
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(26); 
    	row1.createCell(5).setCellValue(cachedDictionaryService.getName(list.get(0).getLoanType(),DataDicType.LOAN_TYPE));
    	row1.getCell(5).setCellStyle(style);
    	
    	row1 = sheet.getRow(27); 
//    	row1.createCell(5).setCellValue(list.get(0).getIcbcStagesFee2()==null?0:list.get(0).getIcbcStagesFee2());
    	row1.createCell(5).setCellValue(list.get(0).getIcbcStagesFee2()==null?0:  Double.parseDouble(convertBigDecimal(Double.parseDouble(String.valueOf(list.get(0).getIcbcStagesFee2())),2)));
    	row1.getCell(5).setCellStyle(style);
    	row1 = sheet.getRow(28);
    	String scp=(signLoanAmount/carPrice)*100+"%";
    	if(scp.contains(".") && scp.length()>=5){
    		scp=scp.substring(0,scp.indexOf(".")+3);
    		
    	}
    	row1.createCell(5).setCellValue(scp);
    	row1.getCell(5).setCellStyle(style);
    	row1 = sheet.getRow(29); 
    	row1.createCell(5).setCellValue(cachedDictionaryService.getName(list.get(0).getColorCar(),DataDicType.CAR_COLOR));
    	row1.getCell(5).setCellStyle(style);
        //创建文件流   
        OutputStream stream;
		try {
			stream = new FileOutputStream(pathTo);
			wb.setForceFormulaRecalculation(true);
			//写入数据   
			wb.write(stream);  
			//关闭文件流   
			stream.close();  
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}  
		
	}
	private String convertBigDecimal(Double dd,int length){
		BigDecimal realdd = new BigDecimal(dd).setScale(10,BigDecimal.ROUND_HALF_UP);
		String ss =  String.valueOf(realdd.setScale(length, RoundingMode.DOWN));
		return ss;
	}
	@ApiOperation(value = "全同套打在提车后就能显示数据",response = YwBusiness.class, httpMethod="POST", tags={"多表查询"})
    @RequestMapping(value = "mulitList", method = RequestMethod.POST)
	public ApiResponse mulitList(@ModelAttribute YwBusinessQueryRequest ywBusinessQueryRequest){
		  SysUser currentUser = (SysUser)getCurrentUser();
		  PageVo page = getPage(ywBusinessQueryRequest);
	      ywBusinessQueryRequest.setBaseWhere();
	      ywBusinessQueryRequest.setBankLoan(currentUser.getLoanBank());
	      ywBusinessQueryRequest.setCodeUnits( currentUser.getCodeUnits());
	      ywBusinessQueryRequest.setBusiStatusLess(BusiStatus.CHE_INFO_REPLAY_OK.getCode());  
	      ywBusinessQueryRequest.setBusiStatusMore(BusiStatus.YIN_HANG_YI_FANG_KUAN.getCode());  
	      int records = ywBusinessService.countMultiByExt(ywBusinessQueryRequest);
	      page.setRecords(records);
	      if (records > 0) {
	          List<YwBusiness> ywBusinesss = ywBusinessService.selectMultiByExt(ywBusinessQueryRequest);
	          ywBusinessVoMapper.mapper(ywBusinesss);
	          DzLoanMiddleAudit dzLoanMiddleAudit=null;
	          for (YwBusiness ywBusiness : ywBusinesss) {
	        	  dzLoanMiddleAudit =new DzLoanMiddleAudit();
	        	  dzLoanMiddleAudit.setBusiId(ywBusiness.getId());
	        	  dzLoanMiddleAudit.setDpStatus(DpStatus.NORMAL.getCode());
	        	  dzLoanMiddleAudit=dzLoanMiddleAuditService.selectRefined(dzLoanMiddleAudit);
	        	  if(dzLoanMiddleAudit!=null){
	        		  ywBusiness.setTimeContractPrint(dzLoanMiddleAudit.getTimeContractPrint());
	        	  }
			}
	          page.setRows(ywBusinesss);
	      } else {
	            page.setRows(CollectionInitUtil.emptyList());
	      }
	      return ApiResponse.success(page);
	}
}
